﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using System.Data;

namespace CIRDiagnoseDevice.DBManage
{
    class DbManage
    {
        public OleDbConnection DbConnection()
        {
            string connStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source =" + Application.StartupPath + @"\" +"DeviceInfo.accdb";
            OleDbConnection conn = new OleDbConnection(connStr);
            return conn;
        }

        public DataTable QueryDb(string sectionName)
        {
            OleDbConnection conn = DbConnection();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            DataRow dr = null;
            string queryStr = "Select * From Device Where CIR编号='" + sectionName + "'";
            //string queryStr = "Select * From Device";
            OleDbCommand command = new OleDbCommand(queryStr, conn);
            command.CommandType = CommandType.Text;
            try
            {
                conn.Open();
                oda.SelectCommand = command;
                oda.Fill(dt);
            }
            catch
            {

            }
            if(dt.Rows.Count !=0)
             dr= dt.Rows[0];
            else
            {
             //MessageBox.Show("此CIR设备不存在");
            }
            conn.Close();
            return dt;
        }

        public void InsertData(string cab_bu,string cab_seg,string cab_pl,string cab_num,int cab_db,string cab_xc,string dev_bu,
                                           string dev_seg, string dev_ws,string dev_wa,string dev_man,string dev_mod,string dev_num, string dev_xc)
        {
            int resultRow = 0;
            string insertStr = "insert into Device(机车属局,机车属段,机车属所,机车编号,机车端别,机车修程,CIR属局," +
                                           "CIR属段,CIR属车间,CIR属工区,CIR生产商,CIR型号,CIR编号,CIR修程) Values(" +
                                           "@cab_bu,@cab_seg,@cab_pl,@cab_num,@cab_db,@cab_xc,@dev_bu," +
                                           "@dev_seg,@dev_ws,@dev_wa,@dev_man,@dev_mod,@dev_num,@dev_xc)";
            string selectStr = "Select * From Device Where CIR编号='" + dev_num + "'";
            OleDbConnection conn = DbConnection();
            OleDbCommand command = new OleDbCommand();
            command.Connection = conn;
            command.CommandType = CommandType.Text;
            command.Parameters.Add("@cab_bu", OleDbType.VarChar);
            command.Parameters["@cab_bu"].Value = cab_bu;
            command.Parameters.Add("@cab_seg",OleDbType.VarChar);
            command.Parameters["@cab_seg"].Value = cab_seg;
            command.Parameters.Add("@cab_pl",OleDbType.VarChar);
            command.Parameters["@cab_pl"].Value = cab_pl;
            command.Parameters.Add("@cab_num",OleDbType.VarChar);
            command.Parameters["@cab_num"].Value = cab_num;
            command.Parameters.Add("@cab_db",OleDbType.Integer);
            command.Parameters["@cab_db"].Value = cab_db;
            command.Parameters.Add("@cab_xc",OleDbType.VarChar);
            command.Parameters["@cab_xc"].Value = cab_xc;
            command.Parameters.Add("@dev_bu",OleDbType.VarChar);
            command.Parameters["@dev_bu"].Value = dev_bu;
            command.Parameters.Add("@dev_seg",OleDbType.VarChar);
            command.Parameters["@dev_seg"].Value = dev_seg;
            command.Parameters.Add("@dev_ws",OleDbType.VarChar);
            command.Parameters["@dev_ws"].Value = dev_ws;
            command.Parameters.Add("@dev_wa",OleDbType.VarChar);
            command.Parameters["@dev_wa"].Value = dev_wa;
            command.Parameters.Add("@dev_man",OleDbType.VarChar);
            command.Parameters["@dev_man"].Value = dev_man;
            command.Parameters.Add("@dev_mod",OleDbType.VarChar);
            command.Parameters["@dev_mod"].Value = dev_mod;
            command.Parameters.Add("@dev_num", OleDbType.VarChar);
            command.Parameters["@dev_num"].Value = dev_num;
            command.Parameters.Add("@dev_xc", OleDbType.VarChar);
            command.Parameters["@dev_xc"].Value = dev_xc;
            command.CommandText = selectStr;
            try
            {
                conn.Open();
                resultRow = command.ExecuteNonQuery();
            }
            catch
            {

            }
            if (resultRow > 0)
            {
                MessageBox.Show("该设备信息已经存在！");
            }
            else
            {
                command.CommandText = insertStr;
                try
                {
                    int result = command.ExecuteNonQuery();
                    if (result == 1)
                    {
                        MessageBox.Show("设备信息添加成功");
                    }
                    else
                        MessageBox.Show("设备信息添加失败");
                }
                catch { }
                finally
                {
                    conn.Close();
                }
            }
        }
       
    }
}
